1. A Method and System For Dynamically Generating Code to Enhance the Performance of a Relational Database Manager That Provides Access to a Relational Database invented by David S. Edwards, David A. Egolf and William L. Lawrance and filed on even date, bearing Ser. No. 09/408,767 and assigned to the same assignee as named herein.
2. A Method and System For Using Dynamically Generated Code to Perform Index Record Retrieval in Certain Circumstances in a Relational Database Manager invented by David S. Edwards and Todd Kneisel, filed on even date, bearing Ser. No. 09/408,986 and assigned to the same assignee as named herein.
1. Field of Use
The present invention relates to data processing systems and more particularly to database management systems.
2. Prior Art
Typically, today""s enterprise or legacy systems store large quantities of data in database systems accessed by database management system (DBMS) software. In such database systems, data is logically organized into relations or tables wherein each relation can be viewed as a table where each row is a tuple and each column is a component of the relation designating an attribute. It has become quite common to use relational database management systems (RDMS) for enabling users to enter queries derived from a database query language, such as SQL, into the database in order to obtain or extract requested data.
In compiling type database management systems, an application program containing database queries is processed for compilation prior to run time. This can be done and more frequently is done at run time by users of the INTEREL product discussed herein. Users of other database products such as DB2, do such processing prior to run time.
During compilation, database queries are passed to the database management system for compilation by a database management system compiler. The compiler translates the queries contained in the application program into machine language. Generally, a database compiler component referred to, as a query optimizer is included in the database management system to select the manner in which queries will be processed. The reason is because most users do not input queries in formats that suggest the most efficient way for the database management system to address the query. The query optimizer component analyzes how best to conduct the user""s query of the database in terms of optimum speed in accessing the requested data. That is, the optimizer typically transforms a user query into an equivalent query that can be computed more efficiently. This operation is performed at compile time, in advance of execution.
A major component of the RDBMS is the database services component or module that supports the functions of SQL language, such as definition, access control, retrieval and update of user and system data. Such components may utilize a multilayer structure containing submodules or components for carrying out the required functions. For example, one such system includes a series of components or conceptually, a series of layers for carrying out the required functions for accessing data from the relational database. More specifically, a first layer functions as a SQL director component that handles requests at the interface to the requesting or calling application program. A second layer consists of two major components, an optimizer for optimizing the query and a RAM code generation component. The optimizer processes the query and determines the appropriate access plan strategy. The code generation component generates code according to such plan for accessing and processing the requested data. The access plan defines the type of access to each table, order of access, whether any sorts or joins are performed along with other related information.
The generated code is passed to a third layer that functions as a relational file manager (RFM) component. This component layer performs the relational file processing function of translating the code-generated requests into IO file read/write requests. A fourth layer that functions as an IO Controller performs the requested I/O operation designated by such IO file requests that results in reading/writing the relational database files in page increments. The described architecture is characteristic of the INTEREL product developed and marketed by Bull HN Information Systems Inc. For information concerning this product, reference may be made to the publication entitled, xe2x80x9cDatabase Products INTEREL Reference Manual INTEREL Performance Guidelines, Copyright, 1996 by Bull HN Information Systems Inc., Order No. LZ93 Rev01B.
It has been found that while the above architecture provides design advantages, it tends to slow down relational data access performance. More specifically, the relational retrieval process involves the execution of functions by a series of components or layers that can result in decreased performance. This is the case particularly when the RDMS is required to access non-partitioned (i.e., single page) data rows.
Accordingly, it is a primary object of the present invention to provide a more efficient method and system for improving relational data access performance in retrieving row data.
The above objects are achieved in a preferred embodiment of the present invention that can be utilized in a relational database management System (RDMS) that implements the Structured Query Language (SQL) standard. The present invention is a system and method that enhances the data access performance of a multi-layered relational database manager in performing row retrieval operations.
The data manager of the preferred embodiment includes several layers; a lower layer of the several layers is a relational file manager (RFM) component layer normally used to perform relational file processing operations, such as the fetching of unpartitioned rows. Since such processing is carried out through a succession of layers, this results in slowing down the row retrieval process. The invention makes it possible to logically remove the RFM component layer from the row retrieval process for most retrieval operations.
According to the teachings of the invention, a higher layer of the data manager that corresponds to the code generation component layer includes specific code that is customized at code generation time for the specific data to be retrieved. The specific code includes a number of calls to a particular performance enhancing subroutine stored in the component""s extended library. The performance enhancing subroutine is designed to execute record management functions performed by lower component layers substantially faster than if such lower component layers executed such functions.
The subroutine includes logic for establishing the conditions under which the particular subroutine is invoked as a function of the characteristics of the particular data record being accessed during the execution of the query. For example, in the preferred embodiment, when the generated SQL query code is being executed and accesses the predetermined type of data file record that corresponds to a non-partitioned row, the code generating component layer executes the performance enhanced subroutine in lieu of having the lower RFM component layer perform the required record file processing. Bypassing or eliminating this layer from the row retrieval process results in a substantial increase in performance.
The above objects and advantages of the present invention will be better understood from the following description when taken in conjunction with the accompanying drawings.